﻿
'========================================================================
'功能:系統基本管控物年資料庫存取物件
'作者:andy
'日期: 2011/2/4
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data
Namespace SYS.DAO
    Public Class sysServerDAO
        Private LockMINUTE As String = "10" '10分鐘

        ''' <summary>
        ''' 取得資料庫
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function MyConn() As SqlConnection
            Dim myConn__1 As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("connStr").ConnectionString)
            Return myConn__1
        End Function

        ''' <summary>
        ''' 鎖定某連線的Session 的順序號
        ''' </summary>
        ''' <param name="ServerIP"></param>
        ''' <param name="SessionID"></param>
        ''' <remarks></remarks>
        Public Sub TryLock(ByVal ServerIP As String, ByVal SessionID As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}

            sqlParams(0) = New SqlParameter("@ServerIP", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = ServerIP
            sqlParams(1) = New SqlParameter("@SessionID", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = SessionID

            Dim sb As New StringBuilder()

            sb.AppendLine("declare @counter int select @counter=count(SeqNo) from sysServerLock   where SessionID=@SessionID  if @counter=0 begin  declare @SeqNo int select @SeqNo=isNull(max(SeqNo),0)+1  from sysServerLock  INSERT INTO [sysServerLock] ( [ServerIP],[SessionID], [SeqNo]) VALUES (")
            sb.AppendLine("@ServerIP, @SessionID, @SeqNo)   end ")
            sb.AppendLine("  Delete from sysServerLock  where CrtDate <=DATEADD (MINUTE ,-" + Me.LockMINUTE + ",GETDATE()) ")
            SqlHelper.ExecuteNonQuery(MyConn(), CommandType.Text, sb.ToString(), sqlParams)
        End Sub
        ''' <summary>
        ''' 解除某一連線
        ''' </summary>
        ''' <param name="SessionID"></param>
        ''' <remarks></remarks>
        Public Sub unLock(ByVal SessionID As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@SessionID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = SessionID

            Dim sb As New StringBuilder()
            sb.AppendLine(" delete  from sysServerLock where SessionID=@SessionID")
            sb.AppendLine("  Delete from sysServerLock  where CrtDate <=DATEADD (MINUTE ,-" + Me.LockMINUTE + ",GETDATE()) ")
            SqlHelper.ExecuteNonQuery(MyConn(), CommandType.Text, sb.ToString(), sqlParams)
        End Sub
        ''' <summary>
        ''' 檢查是否已有Client已經鎖定server,True代表已鎖定不可繼續其他動作
        ''' </summary>
        ''' <param name="SessionID"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function CheckLock(ByVal ServerIP As String, ByVal SessionID As String) As Boolean
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@ServerIP", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = ServerIP
            Dim sb As New StringBuilder()
            sb.AppendLine("  Delete from sysServerLock  where CrtDate <=DATEADD (MINUTE ,-" + Me.LockMINUTE + ",GETDATE()) ")
            sb.AppendLine("   select top 1 *  from sysServerLock where ServerIP=@ServerIP order by SeqNo asc     ")

            Dim dt As DataTable
            dt = SqlHelper.ExecuteDataset(MyConn(), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
            If dt.Rows.Count = 0 Then
                Return False
            End If
            If dt.Rows(0)("SessionID").ToString = SessionID Then
                Return False
            Else
                Return True
            End If

        End Function
    End Class
End Namespace

